﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace BZZ.DAL
{
    /// <summary>
    /// 数据访问类book。
    /// </summary>
    public class book
    {
        iData.DataAcess worker = new iData.DataAcess();
        public book()
        { }

        #region  基本方法（系统生成）

        #region GetMaxId
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            string sql = "select max(id) from BZZ_book";
            return worker.ExecuteCount(sql);
        }
        /// <summary>
        /// 得到最大ID (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public int GetMaxId(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select max(id) from BZZ_book";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            return worker.ExecuteCount(sql);
        }

        #endregion

        #region Exists
        /// <summary>
        /// 是否存在该记录 (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public bool Exists(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select count(*) from BZZ_book";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            int num = worker.ExecuteCount(sql, paraList);
            return num == 0 ? false : true;
        } 
        #endregion

        #region Add
        /// <summary>
        /// 增加一条数据,返回自增的id
        /// </summary>
        public int Add(BZZ.Model.book model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into BZZ_book(");
            strSql.Append("cata,bookName,about,sortid,ext1,ext2)");
            strSql.Append(" values (");
            strSql.Append(" @cata, @bookName, @about, @sortid, @ext1, @ext2)");
            //strSql.Append(";select @@IDENTITY");
            List<SqlParameter> list = new List<SqlParameter>();
			list.Add(new SqlParameter("@cata",model.cata ));
			list.Add(new SqlParameter("@bookName",model.bookName ));
			list.Add(new SqlParameter("@about",model.about ));
			list.Add(new SqlParameter("@sortid",model.sortid ));
			list.Add(new SqlParameter("@ext1",model.ext1 ));
			list.Add(new SqlParameter("@ext2",model.ext2 ));
            return worker.ExecuteInsert(strSql.ToString(), list);
        } 
        #endregion

        #region Update
        /// <summary>
        /// 更新一条数据,返回影响的行数（建议重载后使用）
        /// </summary>
        public int Update(BZZ.Model.book model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update BZZ_book set ");
            strSql.Append("cata=@cata,");
            strSql.Append("bookName=@bookName,");
            strSql.Append("about=@about,");
            strSql.Append("sortid=@sortid,");
            strSql.Append("ext1=@ext1,");
            strSql.Append("ext2=@ext2");
            strSql.Append(" where id=@id ");

            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", model.id));//id
            list.Add(new SqlParameter("@cata", model.cata));//类别
            list.Add(new SqlParameter("@bookName", model.bookName));//书名
            list.Add(new SqlParameter("@about", model.about));//介绍
            list.Add(new SqlParameter("@sortid", model.sortid));//排序
            list.Add(new SqlParameter("@ext1", model.ext1));//扩展1
            list.Add(new SqlParameter("@ext2", model.ext2));//扩展2

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 执行sql语句并返回影响记录条数（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public int Update(string strUpdateSql, List<SqlParameter> cmdParms)
        {
            strUpdateSql = strUpdateSql.Replace("[tbl]", "BZZ_book");
            return worker.ExecuteNonQuery(strUpdateSql, cmdParms);
        }

        #endregion

        #region Delete
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from BZZ_book ");
            strSql.Append(" where id=@id ");
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", id));

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 根据where语句删除数据
        /// </summary>
        public int Delete(string strWhere, List<SqlParameter> paraList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from BZZ_book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            return worker.ExecuteNonQuery(strSql.ToString(), paraList);
        } 
        #endregion

        #region  获得一个字段值
        /// <summary>
        /// 获得数据列表(参数化)
        /// </summary>
        public string GetField(string fieldName, string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 ");
            strSql.Append(fieldName);
            strSql.Append(" FROM BZZ_book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteScalar(strSql.ToString(), cmdParms);
        }


        /// <summary>
        /// 获得记录条数count(*)
        /// </summary>
        public int GetCount(string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) ");
            strSql.Append(" FROM BZZ_book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteCount(strSql.ToString(), cmdParms);
        }
        #endregion

        #region  获得一条记录

        /// <summary>
        /// 用sql语句返回一个datarow（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public DataRow GetRowBySql(string strSelectSql, List<SqlParameter> cmdParams)
        {
            return worker.ExecuteOneRow(strSelectSql, cmdParams);
        }

        /// <summary>
        /// 获得一条记录
        /// </summary>
        public DataRow GetRow(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == null|| dataFields=="")  dataFields = "id,cata,bookName,about,sortid,ext1,ext2";
            strSql.Append("select top 1 ");
            strSql.Append(dataFields);
            strSql.Append(" FROM BZZ_book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteOneRow(strSql.ToString(), cmdParms);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.book GetModel(string strWhere, List<SqlParameter> cmdParms)
        {
            DataRow dr = GetRow(strWhere, cmdParms ,"");
            return dr==null?null:DatarowToModel(dr);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.book GetModel(int id)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@id", id));
            DataRow dr = GetRow("id=@id", paraList ,"");
            return dr==null?null:DatarowToModel(dr);
        }
        #endregion

        #region  获得dataset列表
        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        public DataSet GetDatasetBySql(string strSelectSql)
        {
            return worker.Execute(strSelectSql);
        }

        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        public DataSet GetDatasetBySql(string strSelectSql, List<SqlParameter> cmdParms)
        {
            return worker.Execute(strSelectSql, cmdParms);
        }


        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        /// <param name="tblName">表名，支持多表</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields, string tblName)
        {
            return worker.ExecutePageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, tblName);
        }
        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields)
        {
            return GetDatasetByPageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, "BZZ_book");
        }

        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public DataSet GetDataset(string strWhere)
        {
            return GetDataset(strWhere, null, "");
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,cata,bookName,about,sortid,ext1,ext2";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM BZZ_book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.Execute(strSql.ToString(), cmdParms);
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields,int topN)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,cata,bookName,about,sortid,ext1,ext2";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM BZZ_book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteTop(strSql.ToString(), cmdParms, topN);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public List<Model.book> GetModelList(string strWhere)
        {
            DataSet dset = GetDataset(strWhere, null, "");
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public List<Model.book> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields);
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public List<Model.book> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields, int topN)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields, topN);
            if (dset == null) return null;
            else return DatatableToModelList(dset.Tables[0]);
        }
        #endregion

        #region  change to model
        /// <summary>
        /// 把数据从datatable转存成modellist
        /// </summary>
        public List<Model.book> DatatableToModelList(DataTable dt)
        {
            List<Model.book> modelList = new List<Model.book>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                for (int n = 0; n < rowsCount; n++)
                {
                    Model.book model = new Model.book();
                    model = DatarowToModel(dt.Rows[n]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// 把数据从datarow转存成model
        /// </summary>
        public BZZ.Model.book DatarowToModel(DataRow dr)
        {
            Model.book model = new Model.book();
            model.id = dr["id"] == DBNull.Value ? 0 : int.Parse(dr["id"].ToString());//id
            model.cata = dr["cata"].ToString();//类别
            model.bookName = dr["bookName"].ToString();//书名
            model.about = dr["about"].ToString();//介绍
            model.sortid = dr["sortid"] == DBNull.Value ? 0 : int.Parse(dr["sortid"].ToString());//排序
            model.ext1 = dr["ext1"].ToString();//扩展1
            model.ext2 = dr["ext2"].ToString();//扩展2
            return model;
        }


        #endregion

        #endregion

        #region 自定义方法（非系统生成的方法，请在此区域单独写函数，以便维护）

        /// <summary>
        /// 函数功能：示例函数
        /// 作者：buzhizhe 创建时间：2011.6.22
        /// 修改者：buzhizhe 最后修改时间：2011.6.22
        /// </summary>
        public void YourFunction()
        {
            ///
        } 


        #endregion
    }
}



